using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

namespace ForGVN
{
    public static class Class2
    {
        public static DataTable generateDataForInvoicePrinting(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "XuatThongTinInHoaDon";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@StartDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@EndDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }


        public static DataTable GenerateSurplusReport(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "GenerateSurplusReportForGSA";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable ClaimSP1(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "ClaimSP1";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable ClaimSP2(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "ClaimSP2";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable ClaimSP3(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "ClaimSP3";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable QSReport(string RI, string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();

            if (RI == "Groupama Vietnam")
                comm.CommandText = "QSReportForGVN";
            else if (RI == "VinaRe Vietnam")
                comm.CommandText = "QSReportForVinaRe";
            else
                comm.CommandText = "QSReportForGSA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable GenerateRIFACReport(string RI, string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "GenerateRIFACReport";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@RI";
            param.Value = RI;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TBH_QS_SURPLUS(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TBH_QS_SURPLUS";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@StartDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@EndDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TBH_QS_SURPLUS_Dien(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TBH_QS_SURPLUS_Dien";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@StartDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@EndDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TBH_QS_SURPLUS_Xemay(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TBH_QS_SURPLUS_Xemay";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@StartDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@EndDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TBH_QS_SURPLUS_TatCa(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TBH_QS_SURPLUS_TatCa";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@StartDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@EndDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TBH_QS_SURPLUS_SUM(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TBH_QS_SURPLUS_SUM";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@StartDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@EndDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable generateIBNR(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "generateIBNRv1";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TBH_QS_SURPLUS_Eff(string cuttingDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TBH_QS_SURPLUS_Eff";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@CuttingDate";
            param.Value = cuttingDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TBH_QS_SURPLUS_Eff_Group(string cuttingDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TBH_QS_SURPLUS_Eff_SUM";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@CuttingDate";
            param.Value = cuttingDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TOTAL_BY_COVERAGE(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TOTAL_BY_COVERAGE";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@StartDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@EndDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable TOTAL_BY_COVERAGE_G(string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "TOTAL_BY_COVERAGE_G";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@StartDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@EndDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable RI_BY_A_DATE(string cuttingDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "RI_BY_A_DATE";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@CuttingDate";
            param.Value = cuttingDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);
            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable RI_BY_A_DATE_G(string cuttingDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "RI_BY_A_DATE_G";
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@CuttingDate";
            param.Value = cuttingDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);
            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static void Insert_pl_rate(string ag_ccode, string vn13, string vn14)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "Insert_pl_prate";
            DbParameter pram = comm.CreateParameter();

            pram.ParameterName = "@ag_ccode";
            pram.Value = ag_ccode;
            pram.DbType = DbType.String;
            comm.Parameters.Add(pram);

            pram = comm.CreateParameter();
            pram.ParameterName = "@vn13";
            pram.Value = vn13;
            pram.DbType = DbType.String;
            comm.Parameters.Add(pram);

            pram = comm.CreateParameter();
            pram.ParameterName = "@vn14";
            pram.Value = vn14;
            pram.DbType = DbType.String;
            comm.Parameters.Add(pram);

            GenericDataAccess.ExecuteNonQuery(comm);
        }

        public static void Insert_channel(string channel, string ag_name)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "Insert_channel";
            DbParameter pram = comm.CreateParameter();

            pram.ParameterName = "@channel";
            pram.Value = channel;
            pram.DbType = DbType.String;
            comm.Parameters.Add(pram);

            pram = comm.CreateParameter();
            pram.ParameterName = "@ag_name";
            pram.Value = ag_name;
            pram.DbType = DbType.String;
            comm.Parameters.Add(pram);

            GenericDataAccess.ExecuteNonQuery(comm);
        }


        public static DataTable QSReport_HDThuong(string RI, string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();

            if (RI == "Groupama Vietnam")
                comm.CommandText = "QSReportForGVN_Thuong";
            else if (RI == "VinaRe Vietnam")
                comm.CommandText = "QSReportForVinaRe_Thuong";
            else
                comm.CommandText = "QSReportForGSA_Thuong";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable QSReport_Dien(string RI, string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();

            if (RI == "Groupama Vietnam")
                comm.CommandText = "QSReportForGVN_Dien";
            else if (RI == "VinaRe Vietnam")
                comm.CommandText = "QSReportForVinaRe_Dien";
            else
                comm.CommandText = "QSReportForGSA_Dien";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable QSReport_Xemay(string RI, string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();

            if (RI == "Groupama Vietnam")
                comm.CommandText = "QSReportForGVN_Xemay";
            else if (RI == "VinaRe Vietnam")
                comm.CommandText = "QSReportForVinaRe_Xemay";
            else
                comm.CommandText = "QSReportForGSA_Xemay";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }

        public static DataTable QSReport_ALL(string RI, string fromDate, string toDate)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();

            if (RI == "Groupama Vietnam")
                comm.CommandText = "QSReportForGVN_ALL";
            else if (RI == "VinaRe Vietnam")
                comm.CommandText = "QSReportForVinaRe_ALL";
            else
                comm.CommandText = "QSReportForGSA_ALL";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@FromDate";
            param.Value = fromDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ToDate";
            param.Value = toDate;
            param.DbType = DbType.Date;
            comm.Parameters.Add(param);

            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            return table;
        }
    }
}
